// models/todoModel.js
const db = require('../config/database');

class TodoModel {
  constructor() {
    this.pool = db.createPool();
    this.initializeTable();
  }
  
  async initializeTable() {
    try {
      const createTableQuery = `
        CREATE TABLE IF NOT EXISTS todos (
          id INT AUTO_INCREMENT PRIMARY KEY,
          title VARCHAR(255) NOT NULL,
          completed BOOLEAN DEFAULT false,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
      `;
      
      await this.pool.query(createTableQuery);
      console.log('Todos表初始化成功');
    } catch (error) {
      console.error('数据库初始化失败:', error);
      throw error;
    }
  }
  
  async getAllTodos(filter = {}) {
    try {
      let query = 'SELECT * FROM todos';
      const params = [];
      
      if (filter.completed !== undefined) {
        query += ' WHERE completed = ?';
        params.push(filter.completed ? 1 : 0);
      }
      
      query += ' ORDER BY created_at DESC';
      
      const [rows] = await this.pool.query(query, params);
      return rows;
    } catch (error) {
      console.error('获取待办事项失败:', error);
      throw error;
    }
  }
  
  async createTodo(todoData) {
    try {
      const { title } = todoData;
      
      if (!title) {
        throw new Error('标题是必需的');
      }
      
      const [result] = await this.pool.query(
        'INSERT INTO todos (title) VALUES (?)',
        [title]
      );
      
      const [newTodo] = await this.pool.query(
        'SELECT * FROM todos WHERE id = ?',
        [result.insertId]
      );
      
      return newTodo[0];
    } catch (error) {
      console.error('创建待办事项失败:', error);
      throw error;
    }
  }
  
  async deleteTodo(id) {
    try {
      const [result] = await this.pool.query(
        'DELETE FROM todos WHERE id = ?',
        [id]
      );
      
      if (result.affectedRows === 0) {
        throw new Error('待办事项未找到');
      }
      
      return true;
    } catch (error) {
      console.error('删除待办事项失败:', error);
      throw error;
    }
  }
  
  async updateTodoStatus(id, completed) {
    try {
      const [result] = await this.pool.query(
        'UPDATE todos SET completed = ? WHERE id = ?',
        [completed, id]
      );
      
      if (result.affectedRows === 0) {
        throw new Error('待办事项未找到');
      }
      
      const [updatedTodo] = await this.pool.query(
        'SELECT * FROM todos WHERE id = ?',
        [id]
      );
      
      return updatedTodo[0];
    } catch (error) {
      console.error('更新待办事项状态失败:', error);
      throw error;
    }
  }
}

module.exports = new TodoModel();